//
//  ViewController.m
//  DataBaseTest
//
//  Created by 赵小燕 on 16/12/11.
//  Copyright © 2016年 xiaoyanzhao. All rights reserved.
//

#import "ViewController.h"
#import "sqlite3.h"

@interface ViewController () {
    sqlite3 *dataBase;
    UITextField *_textField;
    UITextField *_textField1;
    UITextField *_textField2;
    UITextField *_textField3;
}


@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    UIButton *button = [self createButton:@"创建数据库" frame:CGRectMake(130, 100, 120, 30)];
    [button addTarget:self action:@selector(createDataBase:) forControlEvents:UIControlEventTouchDown];
    
    UIButton *button1 = [self createButton:@"创建一张表" frame:CGRectMake(130, 160, 120, 30)];
    [button1 addTarget:self action:@selector(createTable:) forControlEvents:UIControlEventTouchDown];
    
    _textField = [[UITextField alloc] initWithFrame:CGRectMake(120, 220, 140, 30)];
    _textField.borderStyle = UITextBorderStyleRoundedRect;
    _textField.placeholder = @"姓名";
    [self.view addSubview:_textField];
    
    _textField1 = [[UITextField alloc] initWithFrame:CGRectMake(120, 260, 140, 30)];
    _textField1.borderStyle = UITextBorderStyleRoundedRect;
    _textField1.placeholder = @"年龄";
    [self.view addSubview:_textField1];
    
    _textField2 = [[UITextField alloc] initWithFrame:CGRectMake(120, 300, 140, 30)];
    _textField2.borderStyle = UITextBorderStyleRoundedRect;
    _textField2.placeholder = @"性别";
    [self.view addSubview:_textField2];
    
    _textField3 = [[UITextField alloc] initWithFrame:CGRectMake(120, 340, 140, 30)];
    _textField3.borderStyle = UITextBorderStyleRoundedRect;
    _textField3.placeholder = @"体重";
    [self.view addSubview:_textField3];
    
    UIButton *button2 = [self createButton:@"插入一行数据" frame:CGRectMake(130, 380, 120, 30)];
    [button2 addTarget:self action:@selector(insertTable:) forControlEvents:UIControlEventTouchDown];
    
    UIButton *button3 = [self createButton:@"删除一行数据" frame:CGRectMake(130, 440, 120, 30)];
    [button3 addTarget:self action:@selector(deleteData:) forControlEvents:UIControlEventTouchDown];
    
    UIButton *button4 = [self createButton:@"修改一行数据" frame:CGRectMake(130, 500, 120, 30)];
    [button4 addTarget:self action:@selector(updateData:) forControlEvents:UIControlEventTouchDown];
    
    UIButton *button5 = [self createButton:@"查找一行数据" frame:CGRectMake(130, 560, 120, 30)];
    [button5 addTarget:self action:@selector(searchData:) forControlEvents:UIControlEventTouchDown];
}

- (UIButton *)createButton:(NSString *)string frame:(CGRect)rect {
    UIButton *button = [UIButton buttonWithType:UIButtonTypeSystem];
    button.frame = rect;
    [button.layer setMasksToBounds:YES];
    [button.layer setCornerRadius:3.0];//设置矩圆角半径,数值越大圆弧越大，反之圆弧越小
    [button.layer setBorderWidth:1.0];//边框宽度
    CGColorSpaceRef colorSpace = CGColorSpaceCreateDeviceRGB();
    CGColorRef colorref = CGColorCreate(colorSpace,(CGFloat[]){ 0, 0, 0, 1 });//R,G,B,alpha
    [button.layer setBorderColor:colorref];
    [button setTitle:string forState:UIControlStateNormal];
    [self.view addSubview:button];
    return button;
}

- (void)createDataBase:(UIButton *)button {
    NSLog(@"start to create DataBase");
    if (sqlite3_open([[self dataFilePath] UTF8String], &dataBase) != SQLITE_OK) {
        sqlite3_close(dataBase);
        NSLog(@"sqlite open failed");
    }
}

- (void)createTable:(UIButton *)button {
    NSLog(@"start to create DataBase");
    NSString *sql = @"CREATE TABLE PERSON_INFO(ID INTEGER PRIMARY KEY, NAME TEXT, AGE INTEGER, SEX TEXT, WEIGHT INTEGER)";
    char *ERROR;
    if (sqlite3_exec(dataBase, [sql UTF8String], NULL, NULL, &ERROR) != SQLITE_OK) {
        sqlite3_close(dataBase);
        NSLog(@"表创建失败");
    }
}

- (void)insertTable:(UIButton *)button {
    NSLog(@"start to create DataBase");
    if (sqlite3_open([[self dataFilePath] UTF8String], &dataBase) != SQLITE_OK) {
        sqlite3_close(dataBase);
        NSLog(@"表打开失败");
    }
    char *insert = "INSERT INTO PERSON_INFO('NAME', 'AGE', 'SEX', 'WEIGHT') VALUES(?, ?, ?, ?)";
    
    sqlite3_stmt *stmt;
    
    if (sqlite3_prepare(dataBase, insert, -1, &stmt, nil) == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, [_textField.text UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 2, [_textField1.text intValue]);
        sqlite3_bind_text(stmt, 3, [_textField2.text UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 4, [_textField3.text intValue]);
    }
    if (sqlite3_step(stmt) != SQLITE_DONE) {
        NSLog(@"数据更新失败");
        
        sqlite3_finalize(stmt);
        sqlite3_close(dataBase);
    }
}

- (void)deleteData:(UIButton *)button {
    NSLog(@"start to delete DataBase");
    if (sqlite3_open([[self dataFilePath] UTF8String], &dataBase) != SQLITE_OK) {
        sqlite3_close(dataBase);
        NSLog(@"表打开失败");
    }
    
    char *error = nil;
    
    NSString *string = [NSString stringWithFormat:@"DELETE FROM PERSON_INFO WHERE NAME = '%@'", [NSString stringWithFormat:@"Pink"]];
    if (sqlite3_exec(dataBase, [string UTF8String], nil, nil, &error) != SQLITE_OK) {
        NSLog(@"数据删除失败");
    }
}

- (void)updateData:(UIButton *)button {
    NSLog(@"Start to update DataBase");
    if (sqlite3_open([[self dataFilePath] UTF8String], &dataBase) != SQLITE_OK) {
        sqlite3_close(dataBase);
        NSLog(@"表打开失败");
    }
    
    char *error = nil;
    NSString *str = [NSString stringWithFormat:@"Update PERSON_INFO SET SEX = '%@' WHERE NAME = '%@'", @"Male", @"Levi"];
    if (sqlite3_exec(dataBase, [str UTF8String], nil, nil, &error) != SQLITE_OK) {
        NSLog(@"更新失败");
    }
}

- (void)searchData:(UIButton *)button {
    NSLog(@"查找数据");
    if (sqlite3_open([[self dataFilePath] UTF8String], &dataBase) != SQLITE_OK) {
        sqlite3_close(dataBase);
        NSLog(@"表打开失败");
    }
    
    char *error = nil;
    NSString *string = [NSString stringWithFormat:@"SELECT ID FROM PERSON_INFO"];
    NSMutableArray *array = [[NSMutableArray alloc] init];
    
}

// 获取当前应用程序沙盒目录
- (NSString *)dataFilePath {
    NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *document = path[0];
    NSString *realPath = [document stringByAppendingPathComponent:@"person_data.db"];
    NSLog(@"111111 %@", realPath);
    return realPath;
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}


@end
